﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using _MVC导入导出EXCEL.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;

namespace _MVC导入导出EXCEL.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }

        public ActionResult ExcelDome()
        {
            return View();
        }

        public ActionResult DownLoadExcel()
        {
            string strSql =
                "Select PersonCode '人员编号',Name '姓名',case sex when 1 then '男' else '女' end '性别',DATEDIFF(YEAR,Birthday,GETDATE()) '年龄' from sqlData..Person";
            DataTable dataSource = SqlUtil.GetDataTable(SqlUtil.ConnString, strSql, CommandType.Text);
            Stream ms = ExcelUtil.DownLoadExcelNew(dataSource);

            return File(ms, "application/zip", "人员信息" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx.zip");

        }


        [HttpPost]
        public ActionResult UpLoadFile()
        {
            HttpPostedFileBase file = Request.Files["ExcelFile"];
            Stream sm = file.InputStream;
            DataTable dt = ExcelUtil.RenderDataTableFromExcel(sm,  StreamType.New);

            return View();
        }

    }
}